package com.typingtutor.dto;

import com.typingtutor.db.DatabaseManager;
import com.typingtutor.model.Document;
import com.typingtutor.model.FieldKeyMappingRelation;
import com.typingtutor.model.FieldMapping;
import org.apache.commons.compress.utils.Lists;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;

public class DocumentDAO extends BaseDAO{
    private static volatile DocumentDAO INSTANCE;
    private DocumentDAO() {
        // 私有构造函数，防止实例化
    }
    public static DocumentDAO getInstance() {
        if (INSTANCE == null) {
            synchronized (DocumentDAO.class) {
                if (INSTANCE == null) {
                    INSTANCE = new DocumentDAO();
                }
            }
        }
        return INSTANCE;
    }
    public String createDocument(String id, String name, String folderId) {
        String sql = "INSERT INTO documents (id, name, folder_id) VALUES (?, ?, ?)";
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, id);
            pstmt.setString(2, name);
            pstmt.setString(3, folderId);
            pstmt.executeUpdate();
            try (ResultSet rs = pstmt.getGeneratedKeys()) {
                if (rs.next()) {
                    return rs.getString(1);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "";
    }

    public String saveDocumentConfiguration(Document document, List<FieldKeyMappingRelation> relations) {
        Connection connection = null;
        try {
            connection = DatabaseManager.getConnection();
            connection.setAutoCommit(false);

            for (FieldKeyMappingRelation relation : relations) {
                String fieldKeyId = relation.getFieldKeyId();
                String fieldMappingId = relation.getFieldMappingId();
                String fieldValue = relation.getFieldMapping().getFieldValue();
                String fieldKey = relation.getFieldKeyInfo().getFieldKey();

                // 处理 field_key_info 表
                if (!isFieldKeyInfoExists(connection, fieldKeyId)) {
                    insertFieldKeyInfo(connection, fieldKeyId, fieldKey);
                }

                // 处理 field_mapping 表
                if (!isFieldMappingExists(connection, fieldMappingId)) {
                    insertFieldMapping(connection, fieldMappingId, fieldValue, document.getId());
                }

                // 处理 field_key_mapping_relation 表
                if (!isRelationExists(connection, fieldKeyId, fieldMappingId)) {
                    insertRelation(connection, fieldKeyId, fieldMappingId);
                }
            }

            connection.commit();
        } catch (SQLException e) {
            // 回滚事务
            if (connection != null) {
                try {
                    connection.rollback();
                    System.out.println("事务回滚！");
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            // 关闭连接
            try {
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    private boolean isFieldKeyInfoExists(Connection connection, String fieldKeyId) throws SQLException {
        String selectSql = "SELECT id FROM field_key_info WHERE id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(selectSql)) {
            pstmt.setString(1, fieldKeyId);
            try (ResultSet rs = pstmt.executeQuery()) {
                boolean next = rs.next();
                return next;
            }
        }
    }

    private void insertFieldKeyInfo(Connection connection, String fieldKeyId, String fieldKey) throws SQLException {
        String insertSql = "INSERT INTO field_key_info (id, field_key, `display`) VALUES (?,?,?)";
        try (PreparedStatement pstmt = connection.prepareStatement(insertSql)) {
            pstmt.setString(1, fieldKeyId);
            pstmt.setString(2, fieldKey);
            pstmt.setBoolean(3, false);
            pstmt.executeUpdate();
        }
    }

    private boolean isFieldMappingExists(Connection connection, String fieldMappingId) throws SQLException {
        String selectSql = "SELECT id FROM field_mapping WHERE id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(selectSql)) {
            pstmt.setString(1, fieldMappingId);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next();
            }
        }
    }

    private void insertFieldMapping(Connection connection, String fieldMappingId, String fieldValue, String documentId) throws SQLException {
        String insertSql = "INSERT INTO field_mapping (id, field_value, document_id) VALUES (?,?,?)";
        try (PreparedStatement pstmt = connection.prepareStatement(insertSql)) {
            pstmt.setString(1, fieldMappingId);
            pstmt.setString(2, fieldValue);
            pstmt.setString(3, documentId);
            pstmt.executeUpdate();
        }
    }

    private boolean isRelationExists(Connection connection, String fieldKeyId, String fieldMappingId) throws SQLException {
        String selectSql = "SELECT field_key_id, field_mapping_id FROM field_key_mapping_relation WHERE field_key_id = ? AND field_mapping_id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(selectSql)) {
            pstmt.setString(1, fieldKeyId);
            pstmt.setString(2, fieldMappingId);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next();
            }
        }
    }

    private void insertRelation(Connection connection, String fieldKeyId, String fieldMappingId) throws SQLException {
        String insertSql = "INSERT INTO field_key_mapping_relation (field_key_id, field_mapping_id) VALUES (?,?)";
        try (PreparedStatement pstmt = connection.prepareStatement(insertSql)) {
            pstmt.setString(1, fieldKeyId);
            pstmt.setString(2, fieldMappingId);
            pstmt.executeUpdate();
        }
    }

    public List<FieldMapping> findConfigurationById(String documentId) {
        String sql = "SELECT * FROM field_mapping WHERE document_id = ?";
        try (Connection conn = DatabaseManager.getConnection();) {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, documentId);
            ResultSet rs = pstmt.executeQuery();
            List<FieldMapping> fieldMappings = Lists.newArrayList();
            while (rs.next()) {
                fieldMappings.add(new FieldMapping(rs.getString("id"),
                        rs.getString("field_value")
                , rs.getString("document_id")));
            }
            return fieldMappings;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    public List<Document> getDocumentsByFolderId(String folderId) {
        List<Document> documents = new ArrayList<>();
        String sql = "SELECT * FROM documents WHERE folder_id = ?";
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, folderId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    String folder = rs.getString("folder_id");
                    documents.add(new Document(id, name, folder));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return documents;
    }

    public List<FieldMapping> findFieldMappingsByFolderId(List<String> folderIds) {
        String ids = folderIds.stream().collect(Collectors.joining(","));
        String selectSql = "SELECT * FROM field_mapping WHERE document_id IN(SELECT id FROM documents WHERE folder_id IN('"+ids+"'))";
        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(selectSql);
            ResultSet rs = pstmt.executeQuery();
            List<FieldMapping> fieldMappings = Lists.newArrayList();
            while (rs.next()) {
                fieldMappings.add(new FieldMapping(rs.getString("id"),
                        rs.getString("field_value"),
                        rs.getString("document_id")));
            }
            return fieldMappings;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    public boolean updateDocument(String documentId, String newName) {
        String sql = "UPDATE documents SET name = ? WHERE id = ?";
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, newName);
            pstmt.setString(2, documentId);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean deleteDocument(String documentId) {
        try (Connection conn = DatabaseManager.getConnection()) {
            // 第一步：删除 field_mapping 表中引用该文档的记录
            String deleteFieldMappingSql = "DELETE FROM field_mapping WHERE document_id = ?";
            try (PreparedStatement pstmt1 = conn.prepareStatement(deleteFieldMappingSql)) {
                pstmt1.setString(1, documentId);
                pstmt1.executeUpdate();
            }

            // 第二步：删除 documents 表中的记录
            String deleteDocumentSql = "DELETE FROM documents WHERE id = ?";
            try (PreparedStatement pstmt2 = conn.prepareStatement(deleteDocumentSql)) {
                pstmt2.setString(1, documentId);
                pstmt2.executeUpdate();
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

}